﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Document.Library
{
    /// <summary>
    /// excel操作帮助类
    /// </summary>
    public class ExcelHelper
    {

        /// <summary>
        /// 将数据导出到Excel
        /// </summary>
        /// <typeparam name="T">导出数据的类型</typeparam>
        /// <param name="dataList">数据集合</param>
        /// <param name="headList">标题集合</param>
        /// <param name="rowValueAction">组合行数据的回调</param>
        /// <returns>Excel的byte数据数组</returns>
        /// <exception cref="Exception"></exception>       
        /// <![CDATA[
        /// var data = new List<dynamic> {
        ///     new { a=1,b = 2,c=3},
        ///     new { a=11,b = 22,c=33},
        ///     new { a=111,b = 222,c=322},
        ///     new { a=1111,b = 2222,c=33333},
        ///     new { a=11111,b = 21231,c=3123132},
        ///     new { a=111111,b = 21231,c=313123},
        /// };
        /// var byteData = Lib.IO.Office.ExcelHelper.ExportToExcel(data, new List<string> { "标题1", "标题2", "标题3" }, rowData =>
        /// {
        ///     return new object[] { rowData.a, rowData.b, rowData.c };
        /// });
        /// 
        /// return File(byteData, "applicationnd.ms-excel", "file.xls");
        /// ]]>
        public static byte[] ExportToExcel<T>(List<T> dataList, List<string> headList, Func<T, object[]> rowValueAction, bool isOrder)
        {
            try
            {
                // 构建文档
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 构建工作簿
                ISheet sheet = workbook.CreateSheet("Sheet1");
                int startIndex = 1;
                int allCount = dataList.Count;
                // 创建标题
                if (isOrder)
                {
                    ICellStyle cellstyle = workbook.CreateCellStyle();//设置垂直居中格式
                    cellstyle.VerticalAlignment = VerticalAlignment.Justify;//垂直对齐(默认应该为center，如果center无效则用justify)
                    cellstyle.Alignment = HorizontalAlignment.Center;//水平对齐

                    // 设置标题数据
                    IRow headerRow = sheet.CreateRow(1);
                    for (int i = 0; i < headList.Count; i++)
                    {
                        headerRow.CreateCell(i).SetCellValue(headList[i]);
                        sheet.AutoSizeColumn(i);
                    }
                    startIndex = 2;
                    allCount = dataList.Count + 1;
                }
                else
                {
                    IRow headerRow = sheet.CreateRow(0);
                    for (int i = 0; i < headList.Count; i++)
                    {
                        headerRow.CreateCell(i).SetCellValue(headList[i]);
                        sheet.AutoSizeColumn(i);
                    }
                }
                // 设置行数据
                for (int i = startIndex; i <= allCount; i++)
                {
                    IRow dataRow = sheet.CreateRow(i);

                    // 回调行数据组装的方法
                    var rowData = rowValueAction(dataList[i - startIndex]);

                    for (int j = 0; j < rowData.Length; j++)
                    {
                        ICell iCell = dataRow.CreateCell(j);
                        if (Regex.IsMatch(Convert.ToString(rowData[j]), @"^\d+$") && rowData[j].ToString().Length < 8)
                        {
                            //是数字
                            iCell.SetCellValue(int.Parse(rowData[j].ToString()));
                        }
                        else if (Regex.IsMatch(Convert.ToString(rowData[j]), @"^(([1-9]+)|([0-9]+\.[0-9]{1,2}))$") && rowData[j].ToString().Length < 8){
                            //是小数字
                            iCell.SetCellValue(double.Parse(rowData[j].ToString()));
                        }
                        else
                        {
                            //不是数字
                            iCell.SetCellValue(Convert.ToString(rowData[j]));
                        }
                    }
                    sheet.AutoSizeColumn(i - startIndex);
                }

                byte[] retBytes = null;

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    retBytes = new byte[ms.Length];
                    ms.Read(retBytes, 0, retBytes.Length);
                }
                return retBytes;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 将数据导出到Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList"></param>
        /// <param name="headList"></param>
        /// <param name="rowValueAction"></param>
        /// <param name="isOrder"></param>
        /// <returns></returns>
        public static string ExportToExcelReturnFilePath<T>(List<T> dataList, List<string> headList, Func<T, object[]> rowValueAction, bool isOrder, string webRootPath, string fileName)
        {
            //虚拟路径
            string path = Path.Combine($"{webRootPath}/Files/Excel/", fileName);
            FileStream fs = null;
            BinaryWriter bw = null;

            try
            {
                // 构建文档
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 构建工作簿
                ISheet sheet = workbook.CreateSheet("Sheet1");
                int startIndex = 1;
                int allCount = dataList.Count;

                #region 表头

                ICellStyle cellstyle = workbook.CreateCellStyle();//设置垂直居中格式
                cellstyle.Alignment = HorizontalAlignment.Center;//水平对齐
                IFont font = workbook.CreateFont(); //创建一个字体样式对象
                font.FontHeightInPoints = 30;
                font.FontName = "微软雅黑";
                font.Boldweight = short.MaxValue;//字体加粗
                cellstyle.SetFont(font); //将字体样式赋给样式对象
 
                IRow headerRow = sheet.CreateRow(0);
                headerRow.RowStyle = cellstyle; //把样式赋给单元格

                for (int i = 0; i < headList.Count; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(headList[i]);
                    sheet.AutoSizeColumn(i);
                }
                #endregion

                // 设置行数据
                for (int i = startIndex; i <= allCount; i++)
                {
                    IRow dataRow = sheet.CreateRow(i);

                    // 回调行数据组装的方法
                    var rowData = rowValueAction(dataList[i - startIndex]);

                    for (int j = 0; j < rowData.Length; j++)
                    {
                        ICell iCell = dataRow.CreateCell(j);
                        if (Regex.IsMatch(Convert.ToString(rowData[j]), @"^\d+$") && rowData[j].ToString().Length < 8)
                        {
                            //是数字
                            iCell.SetCellValue(int.Parse(rowData[j].ToString()));
                        }
                        else if (Regex.IsMatch(Convert.ToString(rowData[j]), @"^(([1-9]+)|([0-9]+\.[0-9]{1,2}))$") && rowData[j].ToString().Length < 8)
                        {
                            //是小数字
                            iCell.SetCellValue(double.Parse(rowData[j].ToString()));
                        }
                        else
                        {
                            //不是数字
                            iCell.SetCellValue(Convert.ToString(rowData[j]));
                        }
                    }
                    sheet.AutoSizeColumn(i - startIndex);
                }

                byte[] retBytes = null;

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    retBytes = new byte[ms.Length];
                    ms.Read(retBytes, 0, retBytes.Length);


                    //不存在文件夹则创建文件夹
                    if (!Directory.Exists($"{webRootPath}/Files/Excel/"))
                    {
                        new DirectoryInfo($"{webRootPath}/Files/Excel/").Create();
                    }

                    fs = new FileStream(path, FileMode.OpenOrCreate);
                    bw = new BinaryWriter(fs);
                    bw.Write(retBytes);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (fs != null) fs.Close();
                if (bw != null) bw.Close();
            }
            return "/Files/Excel/"+ fileName;
        }

        /// <summary>
        /// controll中使用方式   返回值FileResult   return File(Functions.ExportToExcel(dataList,headerList), "applicationnd.ms-excel", "file.xls");
        /// </summary>
        /// <param name="dataList">数据行</param>
        /// <param name="headerList">头部信息，控制输出列</param>
        /// <returns>处理结果</returns>
        public static MemoryStream ExportToExcel(List<Dictionary<string, object>> dataList, Dictionary<string, object> headerList)
        {
            // 构建文档
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 构建工作簿
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 创建标题
            IRow headerRow = sheet.CreateRow(0);

            List<string> hearderKeys = new List<string>(headerList.Keys);

            for (int i = 0; i < hearderKeys.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(headerList[hearderKeys[i]].ToString());
            }

            for (int i = 1; i < dataList.Count + 1; i++)
            {
                IRow dataRow = sheet.CreateRow(i);
                Dictionary<string, object> dataNode = dataList[i - 1];
                for (int j = 0; j < hearderKeys.Count; j++)
                {
                    ICell iCell = dataRow.CreateCell(j);
                    iCell.SetCellValue(Convert.ToString(dataNode[hearderKeys[j]]));
                }
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }


    }
}
